using System;
using System.Data;

using System.Configuration;

using System.Data.OleDb;
using Entity;

namespace SalesVMT
{
    public class UsersDB
    {
        #region PUBLIC
        public static DataSet GetAll()
        {
            string _sql = "Select * from Users";
            return clsConnection.Query(_sql);
        }
        public static DataSet GetList(out int ItemCount, string KeyWord, int currentPage, int pageSize)
        {
            string criteria;
            criteria = " Where 0 = 0 ";
            if (KeyWord.Trim().Length > 0)
            {
                criteria = criteria + " And (Users_Name Like N'%" + KeyWord + "%' )";
            }

            string sql = "";
            DataSet ds = new DataSet();

            sql = "SELECT Count(Users_ID) FROM Users " + criteria;
            ds = clsConnection.Query(sql);
            ItemCount = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

            int StartIndex = currentPage * pageSize + 1;
            int EndIndex = StartIndex + pageSize - 1;

            string OrderBy = "Users_Name";
            string OrderDirection = "DESC";




            //sql = " WITH tmpUsers AS" +
            //                " (SELECT Users.*," +
            //                "   ROW_NUMBER() OVER (ORDER BY " + OrderBy + " " + OrderDirection + ") AS RowNumber" +
            //                "   FROM Users " + criteria + ")" +
            //                " SELECT * FROM tmpUsers" +
            //                " WHERE	RowNumber BETWEEN " + StartIndex.ToString() + " AND " + EndIndex.ToString() +
            //                " ORDER BY RowNumber ASC";

            sql = " SELECT *, (SELECT ROW_NUMBER() OVER (ORDER BY " + OrderBy + " " + OrderDirection + ") FROM USERS " + criteria + ") AS RowNumber  FROM Users " +
                " WHERE	(SELECT ROW_NUMBER() OVER (ORDER BY " + OrderBy + " " + OrderDirection + ") FROM USERS" + criteria + ")" +
                " BETWEEN " + StartIndex.ToString() + " AND " + EndIndex.ToString() +
                " ORDER BY RowNumber ASC";

            ds = clsConnection.Query(sql);
            return ds;
        }
        public static bool Delete(int _Users_ID)
        {
            string _sql = "DELETE FROM USERS WHERE USERS_ID = "+_Users_ID.ToString();
            return clsConnection.Query1(_sql);
        }
        public static int Insert(UsersInfo _UsersInfo)
        {
            int retVal = 0;
            OleDbConnection dbConn = clsConnection.CreateConnection();
            string _sql = "INSERT INTO USERS (Users_Name,Users_PassWord,Users_FullName,Users_Address,Users_Phone,Users_Email, Users_Roles)"+
                            " Values (@Users_Name,@Users_PassWord,@Users_FullName,@Users_Address,@Users_Phone,@Users_Email, @Users_Roles)"+
                            " Select Max(USERS_ID) From USers";
            OleDbCommand dbCmd = new OleDbCommand(_sql, dbConn);

            dbCmd.Parameters.AddWithValue("@Users_Name", _UsersInfo.Users_Name);
            dbCmd.Parameters.AddWithValue("@Users_PassWord", _UsersInfo.Users_PassWord);
            dbCmd.Parameters.AddWithValue("@Users_FullName", _UsersInfo.Users_FullName);
            dbCmd.Parameters.AddWithValue("@Users_Address", _UsersInfo.Users_Address);
            dbCmd.Parameters.AddWithValue("@Users_Phone", _UsersInfo.Users_Phone);
            dbCmd.Parameters.AddWithValue("@Users_Email", _UsersInfo.Users_Email);
            dbCmd.Parameters.AddWithValue("@Users_Roles", _UsersInfo.Users_Roles);

            //dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static bool Update(UsersInfo _UsersInfo)
        {
            bool retVal = false;
            OleDbConnection dbConn = clsConnection.CreateConnection();
            OleDbCommand dbCmd = new OleDbCommand("SalesVMT_Users_Update", dbConn);
            
            dbCmd.Parameters.AddWithValue("@Users_ID", _UsersInfo.Users_ID);
            dbCmd.Parameters.AddWithValue("@Users_Name", _UsersInfo.Users_Name);
            dbCmd.Parameters.AddWithValue("@Users_PassWord", _UsersInfo.Users_PassWord);
            dbCmd.Parameters.AddWithValue("@Users_FullName", _UsersInfo.Users_FullName);
            dbCmd.Parameters.AddWithValue("@Users_Address", _UsersInfo.Users_Address);
            dbCmd.Parameters.AddWithValue("@Users_Phone", _UsersInfo.Users_Phone);
            dbCmd.Parameters.AddWithValue("@Users_Email", _UsersInfo.Users_Email);
            dbCmd.Parameters.AddWithValue("@Users_Roles", _UsersInfo.Users_Roles);

            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static bool ChangePassword(int User_ID, string User_pwd)
        {
            bool retVal = false;
            OleDbConnection dbConn = clsConnection.CreateConnection();
            OleDbCommand dbCmd = new OleDbCommand("SalesVMT_Users_ChangePassword", dbConn);

            dbCmd.Parameters.AddWithValue("@Users_ID", User_ID);
            dbCmd.Parameters.AddWithValue("@Users_PassWord", User_pwd);

            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static UsersInfo GetInfo(int _Users_ID)
        {
            UsersInfo retVal = null;
            OleDbConnection dbConn = clsConnection.CreateConnection();
            OleDbCommand dbCmd = new OleDbCommand("SalesVMT_Users_GetInfo", dbConn);
            dbCmd.Parameters.AddWithValue("@Users_ID", _Users_ID);
            dbCmd.CommandType = CommandType.StoredProcedure;
            OleDbDataReader dr = null;

            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new UsersInfo();
                    retVal.Users_ID = Convert.ToInt32(dr["Users_ID"]);
                    retVal.Users_Name = Convert.ToString(dr["Users_Name"]);
                    retVal.Users_PassWord = Convert.ToString(dr["Users_PassWord"]);
                    retVal.Users_FullName = Convert.ToString(dr["Users_FullName"]);
                    retVal.Users_Address = Convert.ToString(dr["Users_Address"]);
                    retVal.Users_Phone = Convert.ToString(dr["Users_Phone"]);
                    retVal.Users_Email = Convert.ToString(dr["Users_Email"]);
                    retVal.Users_Roles = Convert.ToString(dr["Users_Roles"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public static UsersInfo GetInfoByUsersName(string _Users_Name)
        {
            UsersInfo retVal = null;
            OleDbConnection dbConn = clsConnection.CreateConnection();
            string _sql = "Select * from SalesVMT_Users Where Users_Name = " + _Users_Name.ToString();
            OleDbCommand dbCmd = new OleDbCommand(_sql, dbConn);
            dbCmd.Parameters.AddWithValue("@Users_Name", _Users_Name);
            
            OleDbDataReader dr = null;

            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new UsersInfo();
                    retVal.Users_ID = Convert.ToInt32(dr["Users_ID"]);
                    retVal.Users_Name = Convert.ToString(dr["Users_Name"]);
                    retVal.Users_PassWord = Convert.ToString(dr["Users_PassWord"]);
                    retVal.Users_FullName = Convert.ToString(dr["Users_FullName"]);
                    retVal.Users_Address = Convert.ToString(dr["Users_Address"]);
                    retVal.Users_Phone = Convert.ToString(dr["Users_Phone"]);
                    retVal.Users_Email = Convert.ToString(dr["Users_Email"]);
                    retVal.Users_Roles = Convert.ToString(dr["Users_Roles"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }
        public static DataSet GetInfoByUsersName1(string _Users_Name)
        {
            DataSet retVal = null;
            
            OleDbConnection dbConn = clsConnection.CreateConnection();
            string _sql = "Select * from SalesVMT_Users Where Users_Name = @Users_Name";
            OleDbCommand dbCmd = new OleDbCommand(_sql, dbConn);
            dbCmd.Parameters.AddWithValue("@Users_Name", _Users_Name);
            try
            {
                retVal = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static int Using(int UsersID)
        {
            int retVal = 0;
            OleDbConnection dbConn = clsConnection.CreateConnection();
            OleDbCommand dbCmd = new OleDbCommand("SalesVMT_Users_Using", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Users_ID", UsersID);

            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static bool Exist(int UsersID, string UsersName)
        {
            bool retVal = false;
            OleDbConnection dbConn = clsConnection.CreateConnection();

            OleDbCommand dbCmd = new OleDbCommand("SalesVMT_Users_Exist", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Users_Name", UsersName);
            dbCmd.Parameters.AddWithValue("@Users_ID", UsersID);
            try
            {
                dbConn.Open();
                if (Convert.ToInt32(dbCmd.ExecuteScalar()) > 0)

                    retVal = true;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        #endregion
    }
}
